﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebApplication.DTO;
using System.Data.Common;

namespace WebApplication.DAO
{
    public class csdlDAO
    {
        
        public static List<csdlDTO> ketquaSearch(DbConnection dbconnect,int[] arr,int flagKQ,int page,ref int soLuong)
        {
            if (flagKQ == 1)
            {
                String sql = "SELECT        DULIEU.MaDuLieu, DICHVU.TenDichVu, TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen,";
                sql += " TINHTHANH.TenTinhThanh";
                sql += " FROM ((((((DICHVU INNER JOIN";
                sql += " DULIEU ON DICHVU.MaDichVu = DULIEU.MaDichVu) INNER JOIN";
                sql += " DUONG ON DULIEU.MaDuong = DUONG.MaDuong) INNER JOIN";
                sql += " PHUONG ON DULIEU.MaPhuong = PHUONG.MaPhuong) INNER JOIN";
                sql += " QUANHUYEN ON DULIEU.MaQuanHuyen = QUANHUYEN.MaQuanHuyen) INNER JOIN";
                sql += " TENDIADIEM ON DULIEU.MaTenDiaDiem = TENDIADIEM.MaTenDiaDiem) INNER JOIN";
                sql += " TINHTHANH ON DULIEU.MaTinhThanh = TINHTHANH.MaTinhThanh)";
                if (flagKQ == 1)
                {
                    sql += " Where (";
                }
                for (int i = 0; i < arr.Count(); i++)
                {
                    if (arr[i] != 0)
                    {
                        switch (i)
                        {
                            case 0:
                                sql += "DICHVU.MaDichVu=" + arr[i].ToString() + " AND ";
                                break;
                            case 1:
                                sql += "TENDIADIEM.MaTenDiaDiem=" + arr[i].ToString() + " AND ";
                                break;
                            case 2:
                                sql += "DUONG.MaDuong=" + arr[i].ToString() + " AND ";
                                break;
                            case 3:
                                sql += "PHUONG.MaPhuong=" + arr[i].ToString() + " AND ";
                                break;
                            case 4:
                                sql += "QUANHUYEN.MaQuanHuyen=" + arr[i].ToString() + " AND ";
                                break;
                            case 5:
                                sql += "TINHTHANH.MaTinhThanh=" + arr[i].ToString() + " AND ";
                                break;
                        }
                    }
                }

                if (flagKQ == 1)
                {
                    sql = sql.Substring(0, sql.Count() - 5);
                    sql += " ) GROUP BY DULIEU.MaDuLieu, DICHVU.TenDichVu, TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TINHTHANH.TenTinhThanh";
                }
                
                List<csdlDTO> listCSDL = WebApplication.DataProvider.Provider.excuteReaderSQL_DuLieu(dbconnect, sql,page,ref soLuong);
                return listCSDL;
            }
            return null;
        }

        public static List<dichvuDTO> layTable_DichVu(DbConnection dbconnect)
        {
            String sql = "SELECT        DICHVU.* FROM            DICHVU";
           

            List<dichvuDTO> listDichVu = WebApplication.DataProvider.Provider.excuteReaderSQL_DichVu(dbconnect, sql);
            return listDichVu;
        }

        public static List<diadiemDTO> layTable_DiaDiem(DbConnection dbconnect)
        {
            String sql = "SELECT        TENDIADIEM.* FROM            TENDIADIEM";


            List<diadiemDTO> listDiaDiem = WebApplication.DataProvider.Provider.excuteReaderSQL_DiaDiem(dbconnect, sql);
            return listDiaDiem;
        }

        public static List<duongDTO> layTable_Duong(DbConnection dbconnect)
        {
            String sql = "SELECT        DUONG.* FROM            DUONG";


            List<duongDTO> listDuong = WebApplication.DataProvider.Provider.excuteReaderSQL_Duong(dbconnect, sql);
            return listDuong;

        }

        public static List<phuongDTO> layTable_Phuong(DbConnection dbconnect)
        {
            String sql = "SELECT        PHUONG.* FROM            PHUONG";


            List<phuongDTO> listPhuong = WebApplication.DataProvider.Provider.excuteReaderSQL_Phuong(dbconnect, sql);
            return listPhuong ;
                
        }

        public static List<quanhuyenDTO> layTable_QuanHuyen(DbConnection dbconnect)
        {
            String sql = "SELECT       QUANHUYEN.* FROM            QUANHUYEN";


            List<quanhuyenDTO> listQuanHuyen = WebApplication.DataProvider.Provider.excuteReaderSQL_QuanHuyen(dbconnect, sql);
            return listQuanHuyen;

        }

        public static List<thanhphoDTO> layTable_ThanhPho(DbConnection dbconnect)
        {
            String sql = "SELECT        TINHTHANH.* FROM            TINHTHANH";


            List<thanhphoDTO> listQuanHuyen = WebApplication.DataProvider.Provider.excuteReaderSQL_ThanhPho(dbconnect, sql);
            return listQuanHuyen;

        }

        public static List<tukhoaDTO> layTable_TuKhoaDuong(DbConnection dbconnect)
        {
            String SQL = "SELECT        TUKHOADUONG.TuKhoaDuong, DUONG.TenDuong, TUKHOADUONG.MaDuong FROM            (DUONG INNER JOIN TUKHOADUONG ON DUONG.MaDuong = TUKHOADUONG.MaDuong) GROUP BY TUKHOADUONG.TuKhoaDuong, DUONG.TenDuong, TUKHOADUONG.MaDuong";
            List<tukhoaDTO> listTuKhoa = WebApplication.DataProvider.Provider.excuteReaderSQL_TuKhoa(dbconnect,SQL,0);
            return listTuKhoa;
        }

        public static List<tukhoaDTO> layTable_TuKhoaDiaDiem(DbConnection dbconnect)
        {
            String SQL = "SELECT        TUKHOADIADIEM.TuKhoaTenDiaDiem, TENDIADIEM.TenDiaDiem, TUKHOADIADIEM.MaTenDiaDiem FROM            (TENDIADIEM INNER JOIN TUKHOADIADIEM ON TENDIADIEM.MaTenDiaDiem = TUKHOADIADIEM.MaTenDiaDiem) GROUP BY TUKHOADIADIEM.TuKhoaTenDiaDiem, TENDIADIEM.TenDiaDiem, TUKHOADIADIEM.MaTenDiaDiem";
            List<tukhoaDTO> listTuKhoa = WebApplication.DataProvider.Provider.excuteReaderSQL_TuKhoa(dbconnect,SQL,0);
            return listTuKhoa;
        }

        public static List<tukhoaDTO> layTable_TuKhoaDichVu(DbConnection dbconnect)
        {
            String SQL = "SELECT        TUKHOADICHVU.TuKhoaDichVu, DICHVU.TenDichVu, TUKHOADICHVU.MaDichVu FROM            (DICHVU INNER JOIN TUKHOADICHVU ON DICHVU.MaDichVu = TUKHOADICHVU.MaDichVu) GROUP BY TUKHOADICHVU.TuKhoaDichVu, DICHVU.TenDichVu, TUKHOADICHVU.MaDichVu";
            List<tukhoaDTO> listTuKhoa = WebApplication.DataProvider.Provider.excuteReaderSQL_TuKhoa(dbconnect, SQL,0);
            return listTuKhoa;
        }

        public static List<tukhoaDTO> layTable_TuKhoaPhuong(DbConnection dbconnect)
        {
            String SQL = "SELECT        TUKHOAPHUONG.TuKhoaPhuong, PHUONG.TenPhuong, TUKHOAPHUONG.MaPhuong FROM            (PHUONG INNER JOIN TUKHOAPHUONG ON PHUONG.MaPhuong = TUKHOAPHUONG.MaPhuong) GROUP BY TUKHOAPHUONG.TuKhoaPhuong, PHUONG.TenPhuong, TUKHOAPHUONG.MaPhuong";
            List<tukhoaDTO> listTuKhoa = WebApplication.DataProvider.Provider.excuteReaderSQL_TuKhoa(dbconnect, SQL,3);
            return listTuKhoa;
        }

        public static List<tukhoaDTO> layTable_TuKhoaQuanHuyen(DbConnection dbconnect)
        {
            String SQL = "SELECT        TUKHOAQUANHUYEN.TuKhoaQuanHuyen, QUANHUYEN.TenQuanHuyen, TUKHOAQUANHUYEN.MaQuanHuyen FROM            (QUANHUYEN INNER JOIN TUKHOAQUANHUYEN ON QUANHUYEN.MaQuanHuyen = TUKHOAQUANHUYEN.MaQuanHuyen) GROUP BY TUKHOAQUANHUYEN.TuKhoaQuanHuyen, QUANHUYEN.TenQuanHuyen, TUKHOAQUANHUYEN.MaQuanHuyen";
            List<tukhoaDTO> listTuKhoa = WebApplication.DataProvider.Provider.excuteReaderSQL_TuKhoa(dbconnect, SQL,4);
            return listTuKhoa;
        }

        public static List<tukhoaDTO> layTable_TuKhoaTinhThanh(DbConnection dbconnect)
        {
            String SQL = "SELECT        TUKHOATINHTHANH.TuKhoaTinhThanh, TINHTHANH.TenTinhThanh, TUKHOATINHTHANH.MaTinhThanh FROM            (TINHTHANH INNER JOIN  TUKHOATINHTHANH ON TINHTHANH.MaTinhThanh = TUKHOATINHTHANH.MaTinhThanh) GROUP BY TUKHOATINHTHANH.TuKhoaTinhThanh, TINHTHANH.TenTinhThanh, TUKHOATINHTHANH.MaTinhThanh";
            List<tukhoaDTO> listTuKhoa = WebApplication.DataProvider.Provider.excuteReaderSQL_TuKhoa(dbconnect, SQL,5);
            return listTuKhoa;
        }
       
        public static String gepDuLieu(List<csdlDTO> listCSDL, int index)
        {
            csdlDTO csdl = listCSDL[index]; ;
            String kq = csdl.getTenDichVu() + " " + csdl.getTenDiaDiem() + " " + csdl.getSoNha() + csdl.getPhuong()+ " Quận " + csdl.getQuan() + " Thành Phố " + csdl.getThanhPho();
            return kq;
        }

        
        public static String xuatKetQuaTimDuoc(List<csdlDTO> listCSDL)
        {
            String kq = "";
            for (int i = 0; i < listCSDL.Count; i++)
            {
                csdlDTO CSDL = listCSDL[i];
                String temp = "  ";
                temp += CSDL.getTenDichVu() + " - " + CSDL.getTenDiaDiem() + " - Địa Chỉ : " + CSDL.getSoNha() + " - Đường " + CSDL.getDuong() + " Phường " + CSDL.getPhuong() + " Quận " + CSDL.getQuan() + " - Thành Phố " + CSDL.getThanhPho();
                //kq = kq.Replace(key, "<b><span class=styleBoldResult>" + key + "</span></b>");

                temp += "</span></blockquote></blockquote>";
                temp = "<blockquote><blockquote><img src=/Picture/ded1.png alt= Width=28 Height=32 /><span class=styleResult>" + temp;
                kq += temp;
            }
            return kq;
        }
    }
}